use anyhow::anyhow;
use anyhow::Ok;
use anyhow::Result;
use surrealdb::sql::Id;
use surrealdb::sql::Thing;

use crate::db::FUDB;
use crate::model::trade::trade_bot_with;
use crate::model::trade::trade_bot_with::TradeBot;
use crate::model::FudaRecord;


pub async fn upsert(trade_bot: &trade_bot_with::TradeBot) -> Result<Option<FudaRecord>> {
    let tbws: Vec<TradeBot> = select2(&trade_bot.security_id, &trade_bot.strategy_id, 0, 1).await?;

    if tbws.len() > 0 {
        if let Some(TradeBot { id, .. }) = tbws.get(0) {
            if let Some(Thing { id: Id::String(id_str), .. }) = &id {
                let rs = update(id_str, trade_bot).await?;

                return Ok(rs);
            }
        }
    } else {
        let rs: Option<FudaRecord> = insert(trade_bot).await?;
        return Ok(rs);
    }
    Ok(None)
}

///
/// update or create a trade_bot record
///
/// @param trade_bot: trade_bot's basic information
///
async fn insert(trade_bot: &trade_bot_with::TradeBot) -> Result<Option<FudaRecord>> {
    // query string
    let mut q_str = format!("RELATE $security->trade_bot_with->$strategy");

    let mut has_before = false;
    if let Some(_) = trade_bot.is_running {
        q_str = format!("{} SET is_running=$is_running ", q_str);
        has_before = true;
    }
    if let Some(_) = &trade_bot.last_updated {
        q_str = format!("{} {} last_updated=$last_updated ", q_str, if has_before {
            ","
        } else {
            "SET"
        });
        has_before = true;
    }

    q_str = format!("{} RETURN id", q_str);

    // binding variables
    let mut qq = FUDB.query(&q_str)
        .bind(("security", &trade_bot.security_id))
        .bind(("strategy", &trade_bot.strategy_id));

    if let Some(is_running) = trade_bot.is_running {
        qq = qq.bind(("is_running", is_running));
    }

    if let Some(last_updated) = &trade_bot.last_updated {
        qq = qq.bind(("last_updated", last_updated));
    }

    let mut rs: surrealdb::Response = qq.await.map_err(|e| anyhow::anyhow!(e.to_string()))?;

    let thing: Option<FudaRecord> = rs.take(0)?; // Ensure it's a map

    println!(
        "(trade_bot_with::insert) rs={:?} trade_bot={:?} has_before={} q_str={}, thing={:?}",
        rs,
        trade_bot,
        has_before,
        q_str,
        thing
    );

    Ok(thing)
}

pub async fn update(
    trade_bot_id: &str,
    trade_bot: &trade_bot_with::TradeBot
) -> Result<Option<FudaRecord>> {
    // query string

    let id = Thing::from(("trade_bot_with", trade_bot_id));

    let mut q_str = format!("UPDATE ONLY $id");

    let mut has_before = false;
    if let Some(_) = trade_bot.is_running {
        q_str = format!("{} SET is_running=$is_running ", q_str);
        has_before = true;
    }
    if let Some(_) = &trade_bot.last_updated {
        q_str = format!("{} {} last_updated=$last_updated ", q_str, if has_before {
            ","
        } else {
            "SET"
        });
        has_before = true;
    }

    // binding variables
    let mut qq = FUDB.query(&q_str).bind(("id", &id));

    if let Some(is_running) = trade_bot.is_running {
        qq = qq.bind(("is_running", is_running));
    }

    if let Some(last_updated) = &trade_bot.last_updated {
        qq = qq.bind(("last_updated", last_updated));
    }

    // execute query
    let rs = qq.await.map_err(|e| anyhow::anyhow!(e.to_string()))?;

    println!(
        "(trade_bot_with::update) rs={:?} trade_bot={:?} has_before={} q_str={}",
        rs,
        trade_bot,
        has_before,
        q_str
    );

    Ok(Some(FudaRecord { id }))
}

///
/// delete trade_bot by it's id , e.g. SH.STK.600519
///
pub async fn delete(trade_bot_id: &str) -> Result<()> {
    let mut q_str = format!("delete  $id");

    let mut qq = FUDB.query(&q_str).bind(("id", Thing::from(("trade_bot_with", trade_bot_id))));

    // execute query
    let rs: Result<surrealdb::Response, surrealdb::Error> = qq.await;

    println!("(trade_bot_with::delete) rs={:?} trade_bot_id={} q_str={}", rs, trade_bot_id, q_str);

    Ok(())
}

/// select trade_strategies
///
/// klt: [Klt](super::kline::Klt)
///
/// pi: u16, page index, start from 0
///
/// pn: u16, page size,
///
pub async fn select(
    security_id: &Option<String>,
    strategy_id: &Option<String>,
    pi: u16,
    pn: u16
) -> Result<Vec<trade_bot_with::TradeBot>> {
    let (sec_id, stg_id) = match (security_id, strategy_id) {
        (Some(sec_id), Some(stg_id)) =>
            (
                Some(Thing::from(("security", sec_id.as_str()))),
                Some(Thing::from(("trade_strategy", stg_id.as_str()))),
            ),
        (Some(sec_id), None) => (Some(Thing::from(("security", sec_id.as_str()))), None),
        (None, Some(stg_id)) => (None, Some(Thing::from(("trade_strategy", stg_id.as_str())))),
        (None, None) => (None, None),
    };

    select2(&sec_id, &stg_id, pi, pn).await
}

///
/// find trade_bot by it's id , e.g. z755uu11dfzxqpictna3
///
pub async fn find(trade_bot_id: &str) -> Result<TradeBot> {
    let q_str = format!(
        "SELECT in as security_id, out as strategy_id, *, 
        in.symbol as security_symbol, in.name as security_name, 
        out.name as strategy_name, out.klt as klt, out.script_verified as script_verified, 
        out.script as script, out.kl_window as kl_window, out.indicators as indicators, 
        out.malines as malines
        FROM  $id  FETCH in,out"
    );

    let query = FUDB.query(&q_str).bind(("id", Thing::from(("trade_bot_with", trade_bot_id))));

    // execute query
    let mut response = query.await?;

    // println!("\nresponse= {:?}\n", response);

    let values: Vec<trade_bot_with::TradeBot> = response.take(0)?;

    if values.len() > 0 {
        // filter out indicators and malines
        let mut trade_bot = values[0].clone();

        // Filter out indicators where is_used is false
        if let Some(mut indicators) = trade_bot.indicators {
            indicators.retain(|indicator| indicator.is_used);
            trade_bot.indicators = Some(indicators);
        }

        // Filter out malines where is_used is false
        if let Some(mut malines) = trade_bot.malines {
            malines.retain(|ma_line| ma_line.is_used);
            trade_bot.malines = Some(malines);
        }

        return Ok(trade_bot);
    }

    Err(anyhow!("trade_bot not found"))
}

async fn select2(
    security_id: &Option<Thing>,
    strategy_id: &Option<Thing>,
    pi: u16,
    pn: u16
) -> Result<Vec<trade_bot_with::TradeBot>> {
    let q_str_part = match (&security_id, &strategy_id) {
        (None, None) => r#" ORDER BY security_id,strategy_id "#,
        (Some(_), None) => r#" WHERE in=$security_id ORDER BY security_id,strategy_id "#,
        (None, Some(_)) => r#" WHERE out=$security_id ORDER BY strategy_id,security_id "#,
        (Some(_), Some(_)) => {
            r#" WHERE in=$security_id AND out=$strategy_id ORDER BY security_id,strategy_id "#
        }
    };

    let start = pi * pn;

    let q_str = &format!(
        "SELECT in as security_id, out as strategy_id, *, 
        in.symbol as security_symbol, in.name as security_name, 
        out.name as strategy_name, out.klt as klt, out.script_verified as script_verified 
        FROM  {} {} LIMIT {} START {}  FETCH in,out",
        "trade_bot_with",
        q_str_part,
        pn,
        start
    );

    // Fetch kline data within the specified date range using bind variables
    let mut query = FUDB.query(q_str);

    if let Some(t_) = &security_id {
        query = query.bind(("security_id", t_));
        // Bind the start_date variable
    }

    if let Some(t_) = &strategy_id {
        query = query.bind(("strategy_id", t_));
        // Bind the start_date variable
    }
    // println!("query={:?}", query);

    let mut response = query.await?;

    // println!("\nresponse= {:?}\n", response);

    let values: Vec<trade_bot_with::TradeBot> = response.take(0)?;

    Ok(values)
}

#[cfg(test)]
mod test {
    use chrono::Utc;
    use surrealdb::sql::Thing;

    use crate::db;
    use crate::db::FUHLC;
    use crate::model::kline::Klt;
    use crate::model::trade::trade_bot_with;
    use crate::model::trade::trade_strategy::TradeStrategy;

    #[tokio::test]
    async fn test_upsert_trade_bot() {
        let _ = db::init().await.unwrap();

        for i in 0..103 {
            let trade_bot = trade_bot_with::TradeBot {
                id: None,
                security_id: Some(Thing::from(("security", "SZ.KZZ.128128"))),
                strategy_id: Some(
                    Thing::from((
                        "trade_strategy",
                        TradeStrategy::id_from(&Klt::DAY, i).unwrap().as_str(),
                    ))
                ),
                is_running: if i % 3 == 0 {
                    Some(true)
                } else {
                    Some(false)
                },
                last_updated: Some(Utc::now().to_string()),
                ..Default::default()
            };

            // Update a trade_bot record with a specific id
            let res = db::trade::trade_bot_with::upsert(&trade_bot).await;

            println!("res {:?}", res);
        }
    }

    #[tokio::test]
    async fn test_insert_trade_bot() {
        let _ = db::init().await.unwrap();

        for i in 0..1 {
            let trade_bot = trade_bot_with::TradeBot {
                id: None,
                security_id: Some(Thing::from(("security", "SZ.KZZ.128128"))),
                strategy_id: Some(
                    Thing::from((
                        "trade_strategy",
                        TradeStrategy::id_from(&Klt::DAY, i).unwrap().as_str(),
                    ))
                ),
                is_running: Some(true),
                last_updated: Some(Utc::now().to_string()),
                ..Default::default()
            };

            // Update a trade_bot record with a specific id
            let res = db::trade::trade_bot_with::insert(&trade_bot).await;

            println!("res {:?}", res);
        }
    }

    #[tokio::test]
    async fn test_update_trade_bot() {
        let _ = db::init().await.unwrap();

        let trade_bot = trade_bot_with::TradeBot {
            id: None,
            security_id: Some(Thing::from(("security", "SZ.KZZ.128128"))),
            strategy_id: Some(
                Thing::from((
                    "trade_strategy",
                    TradeStrategy::id_from(&Klt::DAY, 0).unwrap().as_str(),
                ))
            ),
            is_running: Some(false),
            last_updated: Some(Utc::now().to_string()),
            ..Default::default()
        };

        // Update a trade_bot record with a specific id
        let res = db::trade::trade_bot_with::update("z3k6h94y14df668g252o", &trade_bot).await;

        println!("res {:?}", res);
    }

    #[tokio::test]
    async fn test_find_trade_bot() {
        let _ = db::init().await.unwrap();
        let rs = db::trade::trade_bot_with::find("xoysy57cjw8k5fg44oil").await;

        println!("(trade_bot_with::test_find_trade_bot) rs {:?}", rs)
    }

    /// Need to call first: test_update_kline_from_eastmoney to update klines for "513130"
    #[tokio::test]
    async fn test_select2_trade_bot_with() {
        // generate a timestamp
        let ts = FUHLC.new_timestamp();
        println!("\nts = {}", ts.to_string());

        let _ = db::init().await.unwrap();

        let result = db::trade::trade_bot_with::select2(
            &Some(Thing::from(("security", "SZ.KZZ.128128"))),
            &Some(Thing::from(("trade_strategy", "Tstg.DAY.0"))),
            0,
            10
        ).await;

        println!("result= {:?}\n", result);
        assert_eq!(result.is_ok(), true);

        if let Ok(values) = &result {
            for (index, value) in values.iter().enumerate() {
                println!(
                    "[{:02}]= {:?}\n security_id:{:?} strategy_id:{:?}",
                    index,
                    value,
                    value.security_id,
                    value.strategy_id
                );
            }
        }
    }

    #[tokio::test]
    async fn test_delete_trade_bot() {
        let _ = db::init().await.unwrap();
        let rs = db::trade::trade_bot_with::delete("kj50tzjfvkf5i7s55rby").await;

        println!("rs {:?}", rs)
    }
}
